package com.hkbigdata.datagovernance.governance.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.hkbigdata.datagovernance.governance.bean.GovernanceAssessTable;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 * 表治理考评情况 Mapper 接口
 * </p>
 *
 * @author 
 * @since 2024-01-10
 */
@Mapper
@DS("mysqlWriting")
public interface GovernanceAssessTableMapper extends BaseMapper<GovernanceAssessTable> {
    @Select("select " +
            "assess_date," +
            "table_name," +
            "schema_name," +
            "tec_owner," +
            "avg(if(governance_type='SPEC', assess_score, null)) * 10 as score_spec_avg," +
            "avg(if(governance_type='STORAGE',assess_score,null) )*10 as score_storage_avg," +
            "avg(if(governance_type='CALC',assess_score,null) ) * 10 as score_calc_avg," +
            "avg(if(governance_type='QUALITY',assess_score,null) ) * 10 as score_quality_avg," +
            "avg(if(governance_type='SECURITY',assess_score, null) ) * 10 as score_security_avg," +
            "sum(if(assess_score < 10 ,1, 0)) problem_num " +
            "from governance_assess_detail " +
            "where assess_date = #{assessDate} and is_assess_exception = '0' " +
            "group by assess_date, table_name, schema_name, tec_owner" +
            "")
    public List<GovernanceAssessTable> selectGovernanceAssessTable(@Param("assessDate") String assessDate);


}
